﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using CH.Reia.Infrastructure.Entity;
using Newtonsoft.Json.Converters;
using ErrorEventArgs = Newtonsoft.Json.Serialization.ErrorEventArgs;

namespace CH.Reia.Infrastructure.Data
{
    public class DataBase : IDataBase, IDisposable
    {
        private static DataBase caosDataBase;
        private readonly MySqlConnection mySqlConnection;

        private DataBase(String connectionStr)
        {
            this.mySqlConnection = new MySqlConnection(connectionStr);
        }

        public static DataBase CreateDataBase(String connectionStr)
        {
            if ((caosDataBase == null) || !(caosDataBase.mySqlConnection.ConnectionString.Equals(connectionStr)))
            {
                caosDataBase = new DataBase(connectionStr);
            }
            return caosDataBase;
        }

        public List<Object> DatabaseInformation(Object Schema)
        {
            try
            {
                var SchemaStr = Schema.GetType().GetProperty("Schema").GetValue(Schema, null);

                mySqlConnection.Open();

                var sqlStr = @" Select 
                                    *
                                From 
                                    information_schema.Tables 
                                Where 
                                    Table_Schema = '{0}' and Table_Name <> '{0}' ";

                var mySqlCommand = new MySqlCommand(String.Format(sqlStr, SchemaStr, SchemaStr), mySqlConnection);

                var mySqlDataAdapter = new MySqlDataAdapter(mySqlCommand);

                var mySqlDataSet = new DataSet();

                mySqlDataAdapter.Fill(mySqlDataSet);

                var listEntity = new List<Object>();

                foreach (DataRow row in mySqlDataSet.Tables[0].Rows)
                {
                    listEntity.Add(row);
                }

                return listEntity;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                mySqlConnection.Close();
            }
        }

        public Boolean DatabaseDelete(String tableName)
        {
            try
            {
                mySqlConnection.Open();

                var createStr = @" Drop Table `{0}` ";

                var mySqlCommand = new MySqlCommand(String.Format(createStr, tableName), mySqlConnection);

                return (mySqlCommand.ExecuteNonQuery() > 0);
            }
            catch (Exception)
            {
                return true;
            }
            finally
            {
                mySqlConnection.Close();
            }
        }

        public Boolean DomainVerify(String tableName)
        {
            try
            {
                if (mySqlConnection.State != ConnectionState.Open)
                    mySqlConnection.Open();

                var sqlStr = @" Select 
                                    Count(Table_Name) 
                                From 
                                    information_schema.Tables 
                                Where 
                                    Table_Schema = '{0}' And 
                                    Table_Name = '{1}' ";

                var mySqlCommand = new MySqlCommand(String.Format(sqlStr, mySqlConnection.Database, tableName), mySqlConnection);

                return (Convert.ToInt32(mySqlCommand.ExecuteScalar()) > 0);
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                mySqlConnection.Close();
            }
        }

        public Boolean DomainCreate(String tableName)
        {
            try
            {
                mySqlConnection.Open();

                var createStr = @" Create Table `{0}`
                                   (
                                        Identifier Varchar(36),
                                        Type Varchar(36) Not Null,
                                        Content MediumText Not Null,
                                        Primary Key (`Identifier`),
                                        FullText(`Content`)
                                   ) Engine = MyISAM; ";

                var mySqlCommand = new MySqlCommand(String.Format(createStr, tableName), mySqlConnection);

                return (mySqlCommand.ExecuteNonQuery() > 0);
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                mySqlConnection.Close();
            }
        }

        public Object EntityClear(Object entityClear)
        {
            try
            {
                foreach (var propertyInfo in entityClear.GetType().GetProperties())
                {
                    if (propertyInfo.PropertyType.FullName.Contains("List"))
                        propertyInfo.SetValue(entityClear, null, null);
                }

                return entityClear;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public Object EntityLoad(Object entityLoad)
        {
            try
            {
                var listObject = this.EntityFind(entityLoad);

                if (listObject.Count > 1)
                {
                    var identifierStr = string.Concat(entityLoad.GetType().Name, "Id");
                    object entityFind = null;

                    listObject.ForEach(q =>
                                           {
                                               if (q.GetType().GetProperty(identifierStr).GetValue(q, null).Equals(entityLoad.GetType().GetProperty(identifierStr).GetValue(entityLoad, null)))
                                                   entityFind = q;
                                           });

                    return entityFind;
                }
                else
                    return listObject[0];
            }
            catch (Exception)
            {
                return null;
            }
        }

        public List<Object> EntityFind(Object entityFind)
        {
            try
            {
                if (!this.DomainVerify(entityFind.GetType().GetProperty("Domain").GetValue(entityFind, null).ToString()))
                    this.DomainCreate(entityFind.GetType().GetProperty("Domain").GetValue(entityFind, null).ToString());

                mySqlConnection.Open();

                var findStr = @" Select 
                                            Identifier, Type, Content
                                       From 
                                            `{0}`
                                       Where 
                                            {1} ";

                findStr = String.Format(findStr, entityFind.GetType().GetProperty("Domain").GetValue(entityFind, null).ToString(), Criteria.Builder(entityFind));

                var mySqlCommand = new MySqlCommand(findStr, mySqlConnection);

                var mySqlDataAdapter = new MySqlDataAdapter(mySqlCommand);

                var mySqlDataSet = new DataSet();

                mySqlDataAdapter.Fill(mySqlDataSet);

                var listEntity = new List<Object>();

                foreach (DataRow row in mySqlDataSet.Tables[0].Rows)
                {
                    Object entityDeserialize = null;

                    try
                    {
                        entityDeserialize = JsonConvert.DeserializeObject(row["Content"].ToString().Replace("*#@*", "'"), entityFind.GetType());
                    }
                    catch (Exception)
                    {
                        continue;
                    }

                    if (entityFind is IEntity)
                    {
                        ((IEntity)entityDeserialize).Domain = ((IEntity)entityFind).Domain;
                        ((IEntity)entityDeserialize).LazyLoading = ((IEntity)entityFind).LazyLoading;
                        ((IEntity)entityDeserialize).RecordCount = mySqlDataSet.Tables[0].Rows.Count;
                    }

                    listEntity.Add(entityDeserialize);
                }

                if ((entityFind is IEntity) && (((IEntity)entityFind).RecordLimit > 0) && (((IEntity)entityFind).RecordStart > -1))
                {
                    if (((IEntity)entityFind).RecordStart + ((IEntity)entityFind).RecordLimit > mySqlDataSet.Tables[0].Rows.Count)
                        listEntity = listEntity.GetRange(((IEntity)entityFind).RecordStart, mySqlDataSet.Tables[0].Rows.Count - ((IEntity)entityFind).RecordStart);
                    else
                        listEntity = listEntity.GetRange(((IEntity)entityFind).RecordStart, ((IEntity)entityFind).RecordLimit);
                }

                return listEntity;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                mySqlConnection.Close();
            }
        }

        public Boolean EntityUpdate(Object entityUpdate)
        {
            try
            {
                mySqlConnection.Open();

                var updateStr = @" Update `{0}`
                                   Set 
                                        Content = '{1}'
                                   Where 
                                        `Identifier` = '{2}'  ";

                var identifier = entityUpdate.GetType().GetProperty(String.Concat(entityUpdate.GetType().Name, "Id")).GetValue(entityUpdate, null).ToString();

                var jsonContext = JsonConvert.SerializeObject(this.EntityClear(entityUpdate), Formatting.None, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore });

                var mySqlCommand = new MySqlCommand(String.Format(updateStr, entityUpdate.GetType().GetProperty("Domain").GetValue(entityUpdate, null).ToString(), jsonContext, identifier), mySqlConnection);

                return (mySqlCommand.ExecuteNonQuery() > 0);
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                mySqlConnection.Close();
            }
        }

        public Boolean EntitySave(Object entitySave)
        {
            try
            {
                if (!this.DomainVerify(entitySave.GetType().GetProperty("Domain").GetValue(entitySave, null).ToString()))
                    this.DomainCreate(entitySave.GetType().GetProperty("Domain").GetValue(entitySave, null).ToString());

                mySqlConnection.Open();

                var insertStr = @" Insert Ignore into `{0}`
                                   (Identifier, Type, Content)
                                   Values ( '{1}', '{2}', '{3}' ) ";

                var identifier = entitySave.GetType().GetProperty(String.Concat(entitySave.GetType().Name, "Id")).GetValue(entitySave, null).ToString();

                var jsonContext = JsonConvert.SerializeObject(this.EntityClear(entitySave), Formatting.None, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore });

                var mySqlCommand = new MySqlCommand(String.Format(insertStr, entitySave.GetType().GetProperty("Domain").GetValue(entitySave, null), identifier, entitySave.GetType().Name, jsonContext), mySqlConnection);

                return (mySqlCommand.ExecuteNonQuery() > 0);
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                mySqlConnection.Close();
            }
        }

        public Boolean EntityDelete(Object entityDelete)
        {
            try
            {
                mySqlConnection.Open();

                var deleteStr = @" Delete
                                   From 
                                        `{0}`
                                   Where 
                                        `Identifier` = '{1}' ";

                var identifier = entityDelete.GetType().GetProperty(String.Concat(entityDelete.GetType().Name, "Id")).GetValue(entityDelete, null).ToString();

                var mySqlCommand = new MySqlCommand(String.Format(deleteStr, entityDelete.GetType().GetProperty("Domain").GetValue(entityDelete, null), identifier), mySqlConnection);

                return (mySqlCommand.ExecuteNonQuery() > 0);
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                mySqlConnection.Close();
            }
        }

        #region IDisposable Members

        public void Dispose()
        {
            try
            {
                //if (caosDataBase != null)
                //{
                //    caosDataBase.Dispose();
                //}

                //if (mySqlConnection != null)
                //{
                //    mySqlConnection.Dispose();
                //}
            }
            catch (Exception)
            {
            }
        }

        #endregion
    }
}